﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections;

namespace Data
{
    /// <summary>
    /// 管家婆数据处理类
    /// </summary>
    public class DataGrasp
    {
        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public DataGrasp() { }
        #endregion

        #region 管家婆数据变量
        /// <summary>
        /// Employee用户表缓存
        /// </summary>
        public static Data.Model.Grasp36671.Employee[] Employee;
        /// <summary>
        /// Vchtype单据类型缓存
        /// </summary>
        public static Data.Model.Grasp36671.Vchtype[] Vchtype ;
        /// <summary>
        /// 部门表缓存
        /// </summary>
        public static Data.Model.Grasp36671.Department[] Department;
        /// <summary>
        /// 商品表
        /// </summary>
        public static Data.Model.Grasp36671.ptype ptype;

        #endregion


        #region 获取帐套名
        /// <summary>
        /// 获取帐套名
        /// </summary>
        /// <returns></returns>
        public static string[] Get_GraspcwZt()
        {
            try
            {
                int count = 0;
                DataCommon.SqlDataReader1 = DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, "SELECT count([dbname]) FROM [GraspcwZt]");

                while (DataCommon.SqlDataReader1.Read())
                {
                    count = System.Convert.ToInt32(DataCommon.SqlDataReader1[0]);
                }
                
                //string xml = Config.Serialization.Serializer(typeof(DataTable), DataCommon.SqlDataReader1.GetSchemaTable());
                //MessageBox.Show(xml);
                DataCommon.SqlClose();

                DataCommon.SqlDataReader1 = DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, "SELECT [fullname] FROM [GraspcwZt]");
                string[] GraspcwZt = new string[count];
                int count1 = 0;
                while (DataCommon.SqlDataReader1.Read())
                {
                    GraspcwZt[count1] = DataCommon.SqlDataReader1[0].ToString();
                    count1 ++;
                }
                DataCommon.SqlClose();
                return GraspcwZt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据帐套获取数据库名
        /// <summary>
        /// 根据帐套获取数据库名
        /// </summary>
        /// <param name="GraspcwZtName">管家婆帐套名</param>
        /// <returns></returns>
        public static string Get_GraspcwZtData(string GraspcwZtName)
        {
            try
            {
                string returns = "";
                DataCommon.SqlDataReader1 = DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, "SELECT * FROM [GraspcwZt] WHERE [fullname]='" + GraspcwZtName + "'");
                while (DataCommon.SqlDataReader1.Read())
                {
                    returns = DataCommon.SqlDataReader1[1].ToString();
                }
                DataCommon.SqlClose();
                return returns;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据条件获取客户信息
        /// <summary>
        /// 根据条件获取客户信息(电话.名字,拼音码都可以)
        /// </summary>
        /// <param name="Conditions">条件</param>
        /// <returns></returns>
        public static Data.Model.Grasp36671.btype[] GetCustomer(string Conditions)
        {
            //ArrayList returns = new ArrayList();

            Data.Model.Grasp36671.btype[] returns;
            Data.Model.Grasp36671.btype btype = new Model.Grasp36671.btype();
            string sqlstring = "SELECT * FROM " +
                    "[btype] WHERE "+
                    "[FullName] LIKE '%" + Conditions + "%' OR " +
                    "[Person] LIKE '%" + Conditions + "%' OR "+
                    "[Fax] LIKE '%" + Conditions + "%' OR "+
                    "[TelAndAddress] LIKE '%" + Conditions + "%' OR " +
                    "[EMail] LIKE '%" + Conditions + "%' OR " +
                    "[moPhone] LIKE '%" + Conditions + "%' OR " +
                    "[Namepy] LIKE '%" + Conditions + "%' OR " +
                    "[UserCode] LIKE '%" + Conditions + "%'";

            string sqlstringcount = "SELECT count([FullName]) FROM " +
                    "[btype] WHERE " +
                    "[FullName] LIKE '%" + Conditions + "%' OR " +
                    "[Person] LIKE '%" + Conditions + "%' OR " +
                    "[Fax] LIKE '%" + Conditions + "%' OR " +
                    "[TelAndAddress] LIKE '%" + Conditions + "%' OR " +
                    "[EMail] LIKE '%" + Conditions + "%' OR " +
                    "[moPhone] LIKE '%" + Conditions + "%' OR " +
                    "[Namepy] LIKE '%" + Conditions + "%' OR " +
                    "[UserCode] LIKE '%" + Conditions + "%'";

            try
            {
                int t = 0;
                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, sqlstringcount);
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    t = System.Convert.ToInt32(Data.DataCommon.SqlDataReader1[0]);
                }
                 returns = new Model.Grasp36671.btype[t];
                 DataCommon.SqlClose();

                //[FullName],[Person],[Fax],[TelAndAddress],[EMail],[moPhone],[Namepy],[UserCode],[ARTotal],[APTotal],[YRTotal],[YPTotal]
                //搜索包含客户名称,联系人,传真,邮箱,手机,客户拼音码,单位编号,应收余额,应付余额,预收余额,预付余额
                 Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, sqlstring);
                int i = 0;
                while (DataCommon.SqlDataReader1.Read())
                {
                    btype.APTotal = DataCommon.SqlDataReader1["APTotal"].ToString();
                    btype.APTotal00 = DataCommon.SqlDataReader1["APTotal00"].ToString();
                    btype.APTotal00Year = DataCommon.SqlDataReader1["APTotal00Year"].ToString();
                    btype.Area = DataCommon.SqlDataReader1["Area"].ToString();
                    btype.AreaTypeID = DataCommon.SqlDataReader1["AreaTypeID"].ToString();
                    btype.ARTotal = DataCommon.SqlDataReader1["ARTotal"].ToString();
                    btype.ARTotal00 = DataCommon.SqlDataReader1["ARTotal00"].ToString();

                    btype.ARTotal00Year = DataCommon.SqlDataReader1["ARTotal00Year"].ToString();
                    btype.BankAccounts = DataCommon.SqlDataReader1["BankAccounts"].ToString();
                    btype.BankAndAcount = DataCommon.SqlDataReader1["BankAndAcount"].ToString();
                    //btype.ChangeDay = DataCommon.SqlDataReader1["ChangeDay"];

                    btype.ChangeRate = DataCommon.SqlDataReader1["ChangeRate"].ToString();
                    btype.Comment = DataCommon.SqlDataReader1["Comment"].ToString();
                    //btype.deleted = DataCommon.SqlDataReader1["deleted"];

                    btype.EMail = DataCommon.SqlDataReader1["EMail"].ToString();
                    btype.Fax = DataCommon.SqlDataReader1["Fax"].ToString();
                    btype.FullName = DataCommon.SqlDataReader1["FullName"].ToString();
                    //btype.isStop = DataCommon.SqlDataReader1["isStop"];

                    btype.leveal = DataCommon.SqlDataReader1["leveal"].ToString();
                    btype.moPhone = DataCommon.SqlDataReader1["moPhone"].ToString();
                    btype.Name = DataCommon.SqlDataReader1["Name"].ToString();
                    btype.Namepy = DataCommon.SqlDataReader1["Namepy"].ToString();
                    btype.P_WARNUP = DataCommon.SqlDataReader1["P_WARNUP"].ToString();

                    btype.parid = DataCommon.SqlDataReader1["parid"].ToString();
                    btype.ParRec = DataCommon.SqlDataReader1["ParRec"].ToString();
                    btype.Person = DataCommon.SqlDataReader1["Person"].ToString();
                    btype.PostCode = DataCommon.SqlDataReader1["PostCode"].ToString();
                    btype.PriceType = DataCommon.SqlDataReader1["PriceType"].ToString();
                    btype.R_WARNUP = DataCommon.SqlDataReader1["R_WARNUP"].ToString();

                    //btype.Rec = DataCommon.SqlDataReader1["Rec"].ToString();
                    //btype.ROWINDEX = DataCommon.SqlDataReader1["ROWINDEX"].ToString();
                    //btype.SKQX = DataCommon.SqlDataReader1["SKQX"].ToString();
                    //btype.soncount = DataCommon.SqlDataReader1["soncount"].ToString();
                    //btype.sonnum = DataCommon.SqlDataReader1["sonnum"].ToString();
                    btype.TaxNumber = DataCommon.SqlDataReader1["TaxNumber"].ToString();
                    btype.TelAndAddress = DataCommon.SqlDataReader1["TelAndAddress"].ToString();
                    btype.typeId = DataCommon.SqlDataReader1["typeId"].ToString();
                    btype.UserCode = DataCommon.SqlDataReader1["UserCode"].ToString();
                    btype.YPTotal = DataCommon.SqlDataReader1["YPTotal"].ToString();
                    btype.YPTotal00 = DataCommon.SqlDataReader1["YPTotal00"].ToString();
                    btype.YRTotal = DataCommon.SqlDataReader1["YRTotal"].ToString();
                    btype.YRTotal00 = DataCommon.SqlDataReader1["YRTotal00"].ToString();

                    returns[i] = btype;
                    i++;
                    //MessageBox.Show(DataCommon.SqlDataReader1[0].ToString());
                    //returns = DataCommon.SqlDataReader1[1].ToString();
                }
                DataCommon.SqlClose();
                //return returns;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returns;
        }
        #endregion

        #region 根据条件获取客户单据
        /// <summary>
        /// 根据条件获取客户单据
        /// </summary>
        /// <param name="Conditions">客户编号typeId</param>
        /// <returns></returns>
        public static Data.Model.Grasp36671.Dlyndx[] Get_CustomerBills(string Conditions)
        {
            Data.Model.Grasp36671.Dlyndx[] returns;
            string sqlstring =
                "SELECT * FROM [Dlyndx] WHERE [btypeid] LIKE '%" + Conditions + "%'";
            string sqlstringcount =
                "SELECT count([btypeid]) FROM [Dlyndx] WHERE [btypeid] LIKE '%" + Conditions + "%'";
            try
            {
                int count1 = 0;
                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, sqlstringcount);
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    count1 = System.Convert.ToInt32(Data.DataCommon.SqlDataReader1[0]);
                }
                returns = new Model.Grasp36671.Dlyndx[count1];
                DataCommon.SqlClose();

                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, sqlstring);
                int count2 = 0;
                while (DataCommon.SqlDataReader1.Read())
                {
                    Data.Model.Grasp36671.Dlyndx Dlyndx = new Model.Grasp36671.Dlyndx();
                    //单据类型获取
                    for (int VchtypeCount = 0; VchtypeCount < Data.DataGrasp.Vchtype.Length; VchtypeCount++)
                    {
                        if (Data.DataGrasp.Vchtype[VchtypeCount].vchtype == 
                            DataCommon.SqlDataReader1["VchType"].ToString())
                        {
                            Dlyndx.Vchcode = Data.DataGrasp.Vchtype[VchtypeCount].name;
                            //Dlyndx.Vchcode = DataCommon.SqlDataReader1["Vchcode"].ToString();
                            break;
                        }
                    }
                    Dlyndx.Date = DataCommon.SqlDataReader1["Date"].ToString();
                    Dlyndx.Number = DataCommon.SqlDataReader1["Number"].ToString();
                    Dlyndx.VchType = DataCommon.SqlDataReader1["VchType"].ToString();
                    Dlyndx.summary = DataCommon.SqlDataReader1["summary"].ToString();
                    Dlyndx.Comment = DataCommon.SqlDataReader1["Comment"].ToString();
                    Dlyndx.btypeid = DataCommon.SqlDataReader1["btypeid"].ToString();
                    //经手人获取
                    for (int EmployeeCount = 0; EmployeeCount < Data.DataGrasp.Employee.Length; EmployeeCount++)
                    {
                        if (Data.DataGrasp.Employee[EmployeeCount].typeId ==
                            DataCommon.SqlDataReader1["etypeid"].ToString())
                        {
                            Dlyndx.etypeid = Data.DataGrasp.Employee[EmployeeCount].FullName;
                            //Dlyndx.etypeid = DataCommon.SqlDataReader1["etypeid"].ToString()
                            break;
                        }
                    }
                    Dlyndx.ktypeid = DataCommon.SqlDataReader1["ktypeid"].ToString();
                    Dlyndx.ktypeid2 = DataCommon.SqlDataReader1["ktypeid2"].ToString();
                    Dlyndx.ifcheck = DataCommon.SqlDataReader1["ifcheck"].ToString();
                    Dlyndx.checke = DataCommon.SqlDataReader1["checke"].ToString();
                    Dlyndx.period = DataCommon.SqlDataReader1["period"].ToString();
                    Dlyndx.RedWord = DataCommon.SqlDataReader1["RedWord"].ToString();
                    Dlyndx.RedOld = DataCommon.SqlDataReader1["RedOld"].ToString();
                    Dlyndx.accounte = DataCommon.SqlDataReader1["accounte"].ToString();
                    //制单人获取
                    for (int EmployeeCount = 0; EmployeeCount < Data.DataGrasp.Employee.Length; EmployeeCount++)
                    {
                        if (Data.DataGrasp.Employee[EmployeeCount].typeId == 
                            DataCommon.SqlDataReader1["InputNo"].ToString())
                        {
                            Dlyndx.InputNo = Data.DataGrasp.Employee[EmployeeCount].FullName;
                            //Dlyndx.InputNo = DataCommon.SqlDataReader1["InputNo"].ToString();
                            break;
                        }
                    }
                    Dlyndx.draft = DataCommon.SqlDataReader1["draft"].ToString();
                    Dlyndx.cashover = DataCommon.SqlDataReader1["cashover"].ToString();
                    Dlyndx.attach = DataCommon.SqlDataReader1["attach"].ToString();
                    Dlyndx.Total = DataCommon.SqlDataReader1["Total"].ToString();
                    Dlyndx.BillType = DataCommon.SqlDataReader1["BillType"].ToString();
                    Dlyndx.GatheringDate = DataCommon.SqlDataReader1["GatheringDate"].ToString();
                    //部门信息
                    for (int DepartmentCount = 0; DepartmentCount < Data.DataGrasp.Department.Length; DepartmentCount++)
                    {
                        if (Data.DataGrasp.Department[DepartmentCount].typeid == 
                            DataCommon.SqlDataReader1["DeptID"].ToString())
                        {
                            Dlyndx.DeptID = Data.DataGrasp.Department[DepartmentCount].FullName;
                            //Dlyndx.DeptID = DataCommon.SqlDataReader1["DeptID"].ToString();
                            break;
                        }
                    }
                    Dlyndx.AVchCode = DataCommon.SqlDataReader1["AVchCode"].ToString();
                    Dlyndx.counttype = DataCommon.SqlDataReader1["counttype"].ToString();
                    Dlyndx.InputN = DataCommon.SqlDataReader1["InputN"].ToString();
                    Dlyndx.InputNO1 = DataCommon.SqlDataReader1["InputNO1"].ToString();
                    Dlyndx.InputNO2 = DataCommon.SqlDataReader1["InputNO2"].ToString();
                    Dlyndx.InputNO3 = DataCommon.SqlDataReader1["InputNO3"].ToString();
                    Dlyndx.InputNO4 = DataCommon.SqlDataReader1["InputNO4"].ToString();
                    Dlyndx.InputNO5 = DataCommon.SqlDataReader1["InputNO5"].ToString();
                    Dlyndx.InvoceTag = DataCommon.SqlDataReader1["InvoceTag"].ToString();
                    Dlyndx.VipCardID = DataCommon.SqlDataReader1["VipCardID"].ToString();
                    Dlyndx.VipDiscount = DataCommon.SqlDataReader1["VipDiscount"].ToString();
                    Dlyndx.cwFlag = DataCommon.SqlDataReader1["cwFlag"].ToString();
                    Dlyndx.lsFull = DataCommon.SqlDataReader1["lsFull"].ToString();
                    Dlyndx.ChangeTotal = DataCommon.SqlDataReader1["ChangeTotal"].ToString();
                    Dlyndx.lsRetail = DataCommon.SqlDataReader1["lsRetail"].ToString();
                    //过账人
                    for (int EmployeeCount = 0; EmployeeCount < Data.DataGrasp.Employee.Length; EmployeeCount++)
                    {
                        if (Data.DataGrasp.Employee[EmployeeCount].typeId == 
                            DataCommon.SqlDataReader1["PostNo"].ToString())
                        {
                            Dlyndx.PostNo = Data.DataGrasp.Employee[EmployeeCount].FullName;
                            //Dlyndx.PostNo = DataCommon.SqlDataReader1["PostNo"].ToString();
                            break;
                        }
                    }
                    Dlyndx.FromOrderCode = DataCommon.SqlDataReader1["FromOrderCode"].ToString();
                    Dlyndx.SaveCardID = DataCommon.SqlDataReader1["SaveCardID"].ToString();
                    Dlyndx.FeeBtypeID = DataCommon.SqlDataReader1["FeeBtypeID"].ToString();
                    Dlyndx.FeeTotal = DataCommon.SqlDataReader1["FeeTotal"].ToString();
                    Dlyndx.YearPeriod = DataCommon.SqlDataReader1["YearPeriod"].ToString();
                    returns[count2] = Dlyndx;
                    count2 ++;
                }
                DataCommon.SqlClose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returns;
        }
        #endregion

        #region 获取缓存
        /// <summary>
        /// 获取缓存,单据类型缓存,用户缓存,部门信息
        /// </summary>
        /// <returns></returns>
        public static bool Get_Cache()
        {
            bool returns = false;
            //单据类型SQL语句
            string Vchtypestring =
                "SELECT [name],[vchtype] FROM [Vchtype]";
            string Vchtypestringcount =
                "SELECT count([name]) FROM [Vchtype]";
            //用户SQL语句
            string Employeestring =
                "SELECT [typeId],[FullName] FROM [Employee]";
            string Employeestringcount =
                "SELECT count([typeId]) FROM [Employee]";
            //部门SQL语句
            string Departmentstring =
                "SELECT [typeId],[FullName] FROM [Department]";
            string Departmentstringcount =
                "SELECT count([typeid]) FROM [Department]";
            //Department
            int count1 = 0;
            int count2 = 0;
            try
            {
                //获取Vchtype单据类型
                #region 获取Vchtype单据类型缓存
                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, Vchtypestringcount);
                count1 = 0;
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    count1 = System.Convert.ToInt32(Data.DataCommon.SqlDataReader1[0]);
                }
                Data.DataCommon.SqlClose();
                DataGrasp.Vchtype = new Model.Grasp36671.Vchtype[count1];
                Data.Model.Grasp36671.Vchtype Vchtype1;

                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, Vchtypestring);

                count2 = 0;
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    Vchtype1 = new Model.Grasp36671.Vchtype();
                    Vchtype1.name = Data.DataCommon.SqlDataReader1["name"].ToString();
                    Vchtype1.vchtype = Data.DataCommon.SqlDataReader1["vchtype"].ToString();
                    DataGrasp.Vchtype[count2] = Vchtype1;
                    count2++;
                }
                Data.DataCommon.SqlClose();
                #endregion

                #region 获取Employee用户缓存
                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, Employeestringcount);
                count1 = 0;
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    count1 = System.Convert.ToInt32(Data.DataCommon.SqlDataReader1[0]);
                }
                Data.DataCommon.SqlClose();
                DataGrasp.Employee = new Model.Grasp36671.Employee[count1];
                Data.Model.Grasp36671.Employee Employee1;

                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, Employeestring);

                count2 = 0;
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    Employee1 = new Model.Grasp36671.Employee();
                    Employee1.typeId = Data.DataCommon.SqlDataReader1["typeId"].ToString();
                    Employee1.FullName = Data.DataCommon.SqlDataReader1["FullName"].ToString();
                    DataGrasp.Employee[count2] = Employee1;
                    count2++;
                }
                Data.DataCommon.SqlClose();
                #endregion

                #region 获取Department用部门缓存
                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, Departmentstringcount);
                count1 = 0;
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    count1 = System.Convert.ToInt32(Data.DataCommon.SqlDataReader1[0]);
                }
                Data.DataCommon.SqlClose();
                DataGrasp.Department = new Model.Grasp36671.Department[count1];
                Data.Model.Grasp36671.Department Department1;

                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(
                    DataCommon.SqlString, Departmentstring);

                count2 = 0;
                while (Data.DataCommon.SqlDataReader1.Read())
                {
                    Department1 = new Model.Grasp36671.Department();
                    Department1.typeid = Data.DataCommon.SqlDataReader1["typeid"].ToString();
                    Department1.FullName = Data.DataCommon.SqlDataReader1["FullName"].ToString();
                    DataGrasp.Department[count2] = Department1;
                    count2++;
                }
                Data.DataCommon.SqlClose();
                #endregion

                

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returns;
        }
        #endregion

        #region 根据条件获取详细单据
        /// <summary>
        /// 根据条件获取详细单据
        /// </summary>
        /// <param name="Conditions">单据编号typeId</param>
        /// <returns></returns>
        public static Data.Model.Grasp36671.ptype Get_CustomerBillsDetail(string Conditions)
        {

            //8888888888888888888888888888888888888888888888888888
            Data.Model.Grasp36671.ptype returns = new Model.Grasp36671.ptype();
            string sqlstring =
                "SELECT * FROM [Dlyndx] WHERE [ptype] LIKE '%" + Conditions + "%'";//未完成
            try
            {
                Data.DataCommon.SqlDataReader1 = Data.DataCommon.Get_SqlDataReader(DataCommon.SqlString, sqlstring);
                int count2 = 0;
                while (DataCommon.SqlDataReader1.Read())
                {
                    Data.Model.Grasp36671.Dlyndx Dlyndx = new Model.Grasp36671.Dlyndx();

                    //单据类型获取
                    for (int VchtypeCount = 0; VchtypeCount < Data.DataGrasp.Vchtype.Length; VchtypeCount++)
                    {
                        if (Data.DataGrasp.Vchtype[VchtypeCount].vchtype ==
                            DataCommon.SqlDataReader1["VchType"].ToString())
                        {
                            Dlyndx.Vchcode = Data.DataGrasp.Vchtype[VchtypeCount].name;
                            //Dlyndx.Vchcode = DataCommon.SqlDataReader1["Vchcode"].ToString();
                            break;
                        }
                    }
                    Dlyndx.Date = DataCommon.SqlDataReader1["Date"].ToString();
                    
                    //returns[count2] = Dlyndx;
                    count2++;
                }
                DataCommon.SqlClose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returns;
        }
        #endregion



    }
}
